package com.lovedice.database;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.lovedice.R;

public class DBAdapter {
    public static final String KEY_ACTION_ROWID = "_id";
    public static final String KEY_ACTION_NAME = "name";
    public static final String KEY_BODY_ROWID = "_id";
    public static final String KEY_BODY_NAME = "name";
    public static final String KEY_BONUS_ROWID = "_id";
    public static final String KEY_BONUS_NAME = "name";
    
    private static final String TAG = "DBAdapter";
    
    private static final String DATABASE_NAME = "lovedice";
    private static final String DATABASE_TABLE_ACTIONS = "actions";
    private static final String DATABASE_TABLE_BODYS = "bodys";
    private static final String DATABASE_TABLE_BONUS = "bonus";
    private static final int DATABASE_VERSION = 1;

    private static final String DATABASE_CREATE_ACTIONS =
    	"CREATE TABLE "+DATABASE_TABLE_ACTIONS+" ("+
    	KEY_ACTION_ROWID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+ 
    	KEY_ACTION_NAME+" TEXT not null );";
    private static final String DATABASE_CREATE_BODYS =
    	"CREATE TABLE "+DATABASE_TABLE_BODYS+" ("+
    	KEY_BODY_ROWID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+ 
    	KEY_BODY_NAME+" TEXT not null );";
    private static final String DATABASE_CREATE_BONUS =
    	"CREATE TABLE "+DATABASE_TABLE_BONUS+" ("+
    	KEY_BONUS_ROWID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+ 
    	KEY_BONUS_NAME+" TEXT not null );";
    
    private final Context context; 
    
    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;

    public DBAdapter(Context ctx){
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }
        
    private static class DatabaseHelper extends SQLiteOpenHelper 
    {
        private final Context context;

		DatabaseHelper(Context context){
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
            this.context = context;
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(DATABASE_CREATE_ACTIONS);
            db.execSQL(DATABASE_CREATE_BODYS);
            db.execSQL(DATABASE_CREATE_BONUS);

        	restoreFromStringArray(db, context.getResources().getStringArray(R.array.action), KEY_ACTION_NAME, DATABASE_TABLE_ACTIONS);
        	restoreFromStringArray(db, context.getResources().getStringArray(R.array.where), KEY_BODY_NAME, DATABASE_TABLE_BODYS);
        	restoreFromStringArray(db, context.getResources().getStringArray(R.array.incremental), KEY_BONUS_NAME, DATABASE_TABLE_BONUS);
        }
        
		@Override
		public void onOpen(SQLiteDatabase db) {
			super.onOpen(db);
			//onUpgrade(db, DATABASE_VERSION, DATABASE_VERSION);
		}        

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS "+DATABASE_TABLE_ACTIONS);
            db.execSQL("DROP TABLE IF EXISTS "+DATABASE_TABLE_BODYS);
            db.execSQL("DROP TABLE IF EXISTS "+DATABASE_TABLE_BONUS);
            onCreate(db);
        }
        
        public void restoreFromStringArray(SQLiteDatabase db, String[] stringArray, String field, String table){
            final List<String> lista = Arrays.asList( stringArray );
        	for (String item_lista : lista) {
        		ContentValues contentValues = new ContentValues();
        		contentValues.put(field, item_lista);
        		db.insert(table, null, contentValues);
        	}
        }
    }    
    
    public DBAdapter open() throws SQLException{
		db = DBHelper.getWritableDatabase();
        return this;
    }

    public void close() {
		if(db.isOpen())
			db.close();
    }
    
    public boolean isOpen() {
    	return db.isOpen();
    }

    public long insertAction(String name) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_ACTION_NAME, name);
        return db.insert(DATABASE_TABLE_ACTIONS, null, initialValues);
    }

    public long insertBody(String name) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_BODY_NAME, name);
        return db.insert(DATABASE_TABLE_BODYS, null, initialValues);
    }

    public long insertBonus(String name) {
    	ContentValues initialValues = new ContentValues();
    	initialValues.put(KEY_BONUS_NAME, name);
    	return db.insert(DATABASE_TABLE_BONUS, null, initialValues);
    }

    public boolean deleteAction(long rowId) {
    	return db.delete(DATABASE_TABLE_ACTIONS, KEY_ACTION_ROWID + "=" + rowId, null) > 0;
    }
    public boolean deleteBody(long rowId) {
    	return db.delete(DATABASE_TABLE_BODYS, KEY_BODY_ROWID + "=" + rowId, null) > 0;
    }
    public boolean deleteBonus(long rowId) {
    	return db.delete(DATABASE_TABLE_BONUS, KEY_BONUS_ROWID + "=" + rowId, null) > 0;
    }

    public Cursor getActionById(long rowId) throws SQLException {
        Cursor mCursor = db.query(true, DATABASE_TABLE_ACTIONS, new String[] { KEY_ACTION_ROWID, KEY_ACTION_NAME }, 
                		KEY_ACTION_ROWID + "=" + rowId, null, null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }
    public Cursor getBodyById(long rowId) throws SQLException {
    	Cursor mCursor = db.query(true, DATABASE_TABLE_BODYS, new String[] { KEY_BODY_ROWID, KEY_BODY_NAME }, 
    			KEY_BODY_ROWID + "=" + rowId, null, null, null, null, null);
    	if (mCursor != null) {
    		mCursor.moveToFirst();
    	}
    	return mCursor;
    }
    public Cursor getBonusById(long rowId) throws SQLException {
    	Cursor mCursor = db.query(true, DATABASE_TABLE_BONUS, new String[] { KEY_BONUS_ROWID, KEY_BONUS_NAME }, 
    			KEY_BONUS_ROWID + "=" + rowId, null, null, null, null, null);
    	if (mCursor != null) {
    		mCursor.moveToFirst();
    	}
    	return mCursor;
    }

    public Cursor getActionByName(String name) throws SQLException {
        Cursor mCursor = db.query(true, DATABASE_TABLE_ACTIONS, new String[] { KEY_ACTION_ROWID, KEY_ACTION_NAME }, 
                		KEY_ACTION_NAME + "=\"" + name+"\"", null, null, null, null, null);
        if (mCursor != null) { mCursor.moveToFirst(); }
        return mCursor;
    }
    public Cursor getBodyByName(String name) throws SQLException {
    	Cursor mCursor = db.query(true, DATABASE_TABLE_BODYS, new String[] { KEY_BODY_ROWID, KEY_BODY_NAME }, 
    			KEY_BODY_NAME + "=\"" + name+"\"", null, null, null, null, null);
    	if (mCursor != null) { mCursor.moveToFirst(); }
    	return mCursor;
    }
    public Cursor getBonusByName(String name) throws SQLException {
    	Cursor mCursor = db.query(true, DATABASE_TABLE_BONUS, new String[] { KEY_BONUS_ROWID, KEY_BONUS_NAME }, 
    			KEY_BONUS_NAME + "=\"" + name+"\"", null, null, null, null, null);
    	if (mCursor != null) { mCursor.moveToFirst(); }
    	return mCursor;
    }

    public boolean updateAction(long rowId, String name) {
        ContentValues args = new ContentValues();
        args.put(KEY_ACTION_NAME, name);
        return db.update(DATABASE_TABLE_ACTIONS, args, KEY_ACTION_ROWID + "=" + rowId, null) > 0;
    }
    public boolean updateBody(long rowId, String name) {
    	ContentValues args = new ContentValues();
    	args.put(KEY_ACTION_NAME, name);
    	return db.update(DATABASE_TABLE_BODYS, args, KEY_BODY_ROWID + "=" + rowId, null) > 0;
    }
    public boolean updateBonus(long rowId, String name) {
    	ContentValues args = new ContentValues();
    	args.put(KEY_BONUS_NAME, name);
    	return db.update(DATABASE_TABLE_BONUS, args, KEY_BONUS_ROWID + "=" + rowId, null) > 0;
    }
    
    public List<String> retrieveAllActions(){
    	List<String> actions = new ArrayList<String>();
        Cursor allActions = db.query(DATABASE_TABLE_ACTIONS, new String[] { KEY_ACTION_ROWID, KEY_ACTION_NAME}, null, null, null, null, null);
    	if (allActions.moveToFirst()) {
    		do {
    			actions.add(allActions.getString(1));
    		} while (allActions.moveToNext());
    	} else {
    		System.out.println("nada em actions");
    	}
    	if (allActions != null && !allActions.isClosed()) {
    		allActions.close();
    	}
    	return actions;
    }
    public List<String> retrieveAllBodys(){
    	List<String> bodys = new ArrayList<String>();
		Cursor allBodys = db.query(DATABASE_TABLE_BODYS, new String[] { KEY_BODY_ROWID, KEY_BODY_NAME}, null, null, null, null, null);
    	if (allBodys.moveToFirst()) {
    		do {
    			bodys.add(allBodys.getString(1));
    		} while (allBodys.moveToNext());
    	} else {
    		System.out.println("nada em bodys");
    	}
    	if (allBodys != null && !allBodys.isClosed()) {
    		allBodys.close();
    	}
    	return bodys;
    }
    public List<String> retrieveAllBonus(){
    	List<String> bonus = new ArrayList<String>();
		Cursor allBonus = db.query(DATABASE_TABLE_BONUS, new String[] { KEY_BONUS_ROWID, KEY_BONUS_NAME}, null, null, null, null, null);
    	if (allBonus.moveToFirst()) {
    		do {
    			bonus.add(allBonus.getString(1));
    		} while (allBonus.moveToNext());
    	} else {
    		System.out.println("nada em bonus");
    	}
    	if (allBonus != null && !allBonus.isClosed()) {
    		allBonus.close();
    	}
    	return bonus;
    }
    public void exec(String query){
    	db.execSQL(query);
    }
}
